/**
 * 文件名：AdServiceImpl.java
 *
 * 版本信息：
 * 日期：Sep 2, 2009
 * Copyright dp Corporation 2009 
 * 版权所有 2009All rights reserved.
 * 创建者：
 *
 */
package com.dp.service.impl3;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import com.dp.pojo.CommentContent;
import com.dp.pojo.JsonPage;

/**
 * 
 * 项目名称：platform_p2
 * 类名称：AdServiceImpl3
 * 类描述：
 * 创建人：Administrator
 * 创建时间：Sep 2, 2009 8:29:47 PM
 * 修改人：Administrator
 * 修改时间：Sep 2, 2009 8:29:47 PM
 * 修改备注：
 * @version 
 * 
 */
@Service
public class NewscommentServiceImpl3 extends com.dp.service.impl3.BaseImpl {
	private static Logger log = Logger.getLogger(NewscommentServiceImpl3.class);

	/**
	 * 分页
	 * @param jpage
	 * @param commenttext
	 * @param contenttitle
	 * @param delflag
	 * @param begintimepage
	 * @param endtimepage
	 * @param firstrow
	 * @param maxrow
	 * @return
	 */
	public List<CommentContent> findContentComment(JsonPage jpage,String commenttext,String contenttitle,int delflag,String begintimepage,String endtimepage,
			int firstrow, int maxrow) {
		
		String sql = "SELECT nct.contenttitle,nc.id,nc.cid,nc.channleid,nc.clientid,nc.commenttext,nc.name,nc.commenttime,nc.isgood,nc.isdelete,nc.deleter,nc.deletetime,nc.auditer,nc.audittime" +
				" FROM news_comment nc,news_contenttitle nct where isdelete!=2 and nc.cid=nct.cid";
		if (commenttext != null && !"".equals(commenttext)) {
			sql += " and nc.commenttext like '%" + commenttext + "%'";
		}
		if (contenttitle != null && !"".equals(contenttitle)) {
			sql += " and nct.contenttitle like '%" + contenttitle + "%'";
		}
		if (delflag !=9) {	//全部
			sql += " and nc.isdelete =" + delflag;
		}
		if(begintimepage != null && !"".equals(begintimepage)) {
			sql += " and nc.commenttime > '" + begintimepage + "'";
		}
		if(endtimepage != null && !"".equals(endtimepage)) {
			sql += " and nc.commenttime < '" + endtimepage + "'";
		}
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				if("contentname".equals(jpage.getSidx())){
					sql+=" order by nct.contenttitle";
				}else{
					sql+=" order by nc."+jpage.getSidx();
				}
			    sql+=" "+jpage.getSord();
			    log.info("jpage.getSord()返回的值是"+jpage.getSord());
			}
		}
		sql += " limit " +firstrow+","+maxrow;
		log.info(" findContentComment excute sql : " +sql);
		List result = new ArrayList();;
		try {
//			this.init();
//			this.rs1 = stmt.executeQuery(sql);
//			while(rs1.next())
//			{	
//				int j = 1;
//				CommentContent cc = new CommentContent();
//				cc.setContentname(rs1.getString(j++));
//				cc.setId(rs1.getInt(j++));
//				cc.setCid(rs1.getInt(j++));
//				cc.setChannleid(rs1.getInt(j++));
//				cc.setClientid(rs1.getString(j++));
//				cc.setCommenttext(rs1.getString(j++));
//				cc.setName(rs1.getString(j++));
//				Date t = new Date(rs1.getTimestamp(j++).getTime());
//				cc.setCommenttime(t);
//				cc.setIsgood(rs1.getInt(j++));
//				cc.setIsdelete(rs1.getInt(j++));
//				cc.setDeleter(rs1.getString(j++));
//				Timestamp ts = rs1.getTimestamp(j++);
//				if(ts != null)
//				{
//					t = new Date(ts.getTime());
//					cc.setDeletetime(t);
//				}
//				cc.setAuditer(rs1.getString(j++));
//				ts = rs1.getTimestamp(j++);
//				if(ts != null)
//				{
//					t = new Date(ts.getTime());
//					cc.setAudittime(t);
//				}
//				
//				result.add(cc);
//			}
//			this.closeAll();
		} catch (Exception e) {
			log.error(" findContentComment fail !!!" +e.getMessage());
		}
	
		return result;
	}
	
	/**
	 * 分页
	 * @param commenttext
	 * @param contenttitle
	 * @param delflag
	 * @param begintimepage
	 * @param endtimepage
	 * @return
	 */
	public int findContentCommentCount(String commenttext, String contenttitle,int delflag,String begintimepage,String endtimepage) {
		String sql = "SELECT count(nc.id) FROM news_comment nc,news_contenttitle nct where isdelete!=2 and nc.cid=nct.cid";
		if (commenttext != null && !"".equals(commenttext)) {
			sql += " and nc.commenttext like '%" + commenttext + "%'";
		}
		if (contenttitle != null && !"".equals(contenttitle)) {
			sql += " and nct.contenttitle like '%" + contenttitle + "%'";
		}
		if (delflag != 9) {
			sql += " and nc.isdelete =" + delflag;
		}
		if(begintimepage != null && !"".equals(begintimepage)) {
			sql += " and nc.commenttime > '" + begintimepage + "'";
		}
		if(endtimepage != null && !"".equals(endtimepage)) {
			sql += " and nc.commenttime < '" + endtimepage + "'";
		}
		log.info(" findContentComment excute sql : " +sql);
		int i = 0;
		try {
			i=this.getOneInt();
		} catch (Exception e) {
			log.error(" findContentCommentCount fail !!!" +e.getMessage());
		}
		
		return i;
	}
	
	//根据sql查出第一列，并以","分隔。
	private String getFirstColumnStringsBySql(String sql){
		String cids = "";
		try {
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
				String value = this.getString(o[0]);
				cids += value+",";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cids;
	}
	
	/**
	 * 审核通过
	 * @param ids
	 * @param user
	 * @return
	 */
	public boolean auditAllNewsComment(String ids, String user)
	{
		boolean flag = false;
		try {
		
			String notids = getFirstColumnStringsBySql("SELECT id FROM news_comment WHERE (isdelete IS NULL OR isdelete<>3) AND id IN ("+ids+")");	//审核未通过的
			if(notids!=null && !"".equals(notids)){
				String sql = "update news_comment set audittime=now(),isdelete = 3,auditer='"+user+"' where id in (" + notids.substring(0,notids.length()-1)+")";
				log.info(" auditAllNewsComment excute sql : " +sql);
				this.updateNativeQuery(sql);
			}
			flag = true;
		} catch (Exception e) {
			log.info(" auditAllNewsComment fail !!!" +e.getMessage());
			e.printStackTrace();
		} 
		return flag;
	}

	/**
	 * 设为精华
	 * @param newCommentIdList
	 * @param user
	 * @return
	 */
	public boolean goodAllNewsComment(String ids, String user)
	{
		boolean flag = false;
		 sql = "update news_comment set isgood=2 where id in (" + ids+")";
		log.info("sql:"+sql);
		try {
			this.updateNativeQuery(sql);
			String cids = getFirstColumnStringsBySql("SELECT cid FROM news_comment WHERE (isgood IS NULL OR isgood<>2) AND id IN ("+ids+")");	//不为精华
			if(cids!=null && !"".equals(cids)){
				String sqlTitle = "update news_contenttitle set updatetime=now() where cid in (" + cids.substring(0,cids.length()-1)+")";
				log.info("sqltitle:"+sqlTitle);
				this.updateNativeQuery(sql);
			}
			
			flag = true;
		} catch (Exception e) {
			
			log.error(" goodAllNewsComment fail !!!" +e.getMessage());
		}
		return flag;
	}
	
	/**
	 * 删除评论信息
	 * @param sid
	 * @return boolean
	 */
	public boolean deleteAllNewsCommentByIds(String ids,String user) {
		Boolean flag=false;
		try {
		
			String notids = getFirstColumnStringsBySql("SELECT id FROM news_comment WHERE (isdelete IS NULL OR isdelete<>2) AND id IN ("+ids+")");	//未被删除的
			if(notids!=null && !"".equals(notids)){
				this.sql="update news_comment set deletetime=now(),isdelete = 2,deleter='"+user+"' where id in ("+notids.substring(0,notids.length()-1)+")";
				this.updateNativeQuery(sql);
			}
			flag = true;
		} catch (Exception e) {
			logger.error("deleteDeviceExtendById fail !!!" + e.getMessage());
		} 
		return flag;
	}
	
}
